﻿Imports MySql.Data.MySqlClient
Imports System.IO
Imports DevExpress.XtraGrid.Views.Grid
Public Class frmPPFP
    Dim DA As New MySqlDataAdapter
    Dim MyConn As New MySqlConnection
    Dim mysql As New ClsSQLhelper
    Dim DSOPPP As New OPPP
    Dim Strsql As String
    Dim Curr_View As String = "Default"
    Dim On_load As Boolean = True
    Dim StrMainSql As String = "select m_fp.PID  ,m_person.CID,m_person.HN,Concat(m_person.NAME,'  ',m_person.LNAME)" & _
" as NAME,m_person.SEX,m_person.BIRTH,m_person.MSTATUS as MSTATUS,m_person.OCCUPA,m_person.NATION,m_person.TYPEAREA," & _
" m_fp.SEQ,m_fp.DATE_SERV,m_fp.FPTYPE,m_fp.DID,m_fp.AMOUNT,m_fp.FPPLACE,m_fp.CODE_ID  " & _
" from m_fp left join m_person on  m_fp.PID = m_person.PID  "
    
#Region "Function"

    Sub GenScript()
        Try
            Dim Strsql As String = " CID in ("
            Dim HNSql As String = " HN in ("
            Dim PIDsql As String = " person_id in ("
            If Me.DataGridView1.Rows.Count = 0 Then Exit Sub
            Select Case Me.Curr_View


                Case Else
                    Strsql = " CID in ("
                    For i As Integer = 0 To DataGridView1.RowCount - 1

                        Strsql = Strsql & "'" & DataGridView1.Rows(i).Cells("CID").Value.ToString.Trim() & "',"
                        HNSql = HNSql & "'" & DataGridView1.Rows(i).Cells("HN").Value.ToString.Trim() & "',"
                        PIDsql = PIDsql & "'" & DataGridView1.Rows(i).Cells("PID").Value.ToString.Trim() & "',"
                    Next
                    Strsql = Mid(Strsql, 1, Len(Strsql) - 1) & ")"
                    HNSql = Mid(HNSql, 1, Len(HNSql) - 1) & ")"
                    PIDsql = Mid(PIDsql, 1, Len(PIDsql) - 1) & ")"
                    SQLDialog.Sqltext.Text = Strsql
                    SQLDialog.Sqltext2.Text = HNSql
                    SQLDialog.Sqltext3.Text = PIDsql

                    SQLDialog.ShowDialog()

            End Select

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Sub Load_Data(ByVal Code_ID As String, Optional ByVal StrAddonSql As String = "")
        Try

            Me.Cursor = Cursors.WaitCursor
            Me.Curr_View = Code_ID
            Strsql = StrMainSql & " where m_fp.CODE_ID  like  '%" & Code_ID & "%'  " & " " & StrAddonSql & "  and m_fp.FILENAME = " & Me.CboImportOPFile.SelectedValue
            'If Me.ChkShowPIDError.Checked = False Then
            '    Strsql = Strsql & " and  m_mch.pid <> 000000 "
            'End If
            'If Me.ChkShowNewBorn.Checked = False Then
            '    Strsql = Strsql & " and  m_person.NAME not like '(%' "
            'End If
            Strsql = Strsql & " order by  m_fp.PID desc"


            '  DA = New MySqlDataAdapter(Strsql, MyConn)
            ' DA.Fill(DSOPPP, "m_person")
            Dim DT_Person As New DataTable
            DT_Person = mysql.GetMYSQLDataTable(Strsql, "m_fp")
            With DataGridView1

                .RowHeadersVisible = False
                .AllowDrop = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToOrderColumns = True
                .AllowUserToResizeColumns = True
                .AllowUserToResizeRows = False
                .MultiSelect = False
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                .DataSource = DT_Person

                If .RowCount < 1 Then
                    MsgBox("ไม่พบข้อมูล", MsgBoxStyle.Exclamation)
                Else
                    MsgBox("ดึงข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)
                End If
            End With
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.Message)
        End Try

    End Sub
    Sub Load_Summary()
        Try
            Strsql = "select CODE_ID,Count(CODE_ID) as Totalrows from m_fp where filename = " & CboImportOPFile.SelectedValue & " and code_id is not null  group by code_id "
            Dim DT_Service As New DataTable
            DT_Service = mysql.GetMYSQLDataTable(Strsql, "ERROR_Summary")

            With Grid_ErrorCode
                .DataSource = DT_Service

            End With
            With DataGridView1

            End With
            'With ErrorGrid
            '    .RowHeadersVisible = False
            '    .AllowDrop = False
            '    .AllowUserToAddRows = False
            '    .AllowUserToDeleteRows = False
            '    .AllowUserToOrderColumns = True
            '    .AllowUserToResizeColumns = True
            '    .AllowUserToResizeRows = False
            '    .MultiSelect = False
            '    .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
            '    .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
            '    .DataSource = DT_Service 'DSOPPP.Tables("m_person")
            '    ' If ShowCompleteMsg = True Then
            '    'MsgBox("ดึงข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)
            '    'End If

            'End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Sub Search_Information()
        Try
            If Me.VGR_PID.Properties.Value = "000000" Then
                MsgBox("ไม่สามารถหาความเชื่อมโยงได้เนื่องจาก PID = 000000")
                Exit Sub
            End If
            Select Case Me.VGR_CID.Properties.Value
                Case "0000000000000"
                    If MsgBox("รหัสบัตรไม่ถูกกต้อง คุณยืนยันที่จะค้นหาต่อหรือไม่", MsgBoxStyle.Exclamation + MsgBoxStyle.YesNo, "คำเตือน") = MsgBoxResult.No Then Exit Sub
                    frmSearchInfo.TxtCriteria.Text = Me.VGR_PID.Properties.Value
                Case ""
                    If Me.VGR_HN.Properties.Value <> "ไม่ระบุ" Then

                        frmSearchInfo.TxtCriteria.Text = Me.VGR_HN.Properties.Value
                    Else
                        If Me.VGR_PID.Properties.Value <> "" Then
                            frmSearchInfo.TxtCriteria.Text = Me.VGR_PID.Properties.Value
                        ElseIf Me.VGR_Fullname.Properties.Value <> "" Then
                            frmSearchInfo.TxtCriteria.Text = Me.VGR_Fullname.Properties.Value
                        Else

                            MsgBox("ไม่สามารถ หาความเชื่อมโยงได้", MsgBoxStyle.Critical)
                        End If

                    End If
                Case Else
                    frmSearchInfo.TxtCriteria.Text = Me.VGR_CID.Properties.Value
            End Select
            With frmSearchInfo
                .TxtYear.Text = Me.TxtYear.Text
                .TxtMonth.Text = Me.TxtMonth.Text
                '.TxtDateServ.Text = Me.TxtDateServ.Text
                .Submit_Search()
                .ShowDialog()
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
#End Region
    Private Sub frmPPFP_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Me.VGR_CID.Properties.Value = "0000000000000"
            Dim DT_m_files_upload As DataTable
            Strsql = "select ROWID,FILENAME from m_files_upload "
            Strsql = Strsql & " where TYPE_DESC = 'PP Package' or  TYPE_DESC = '18 แฟ้ม' "
            Strsql = Strsql & " order by Date_SEND DESC"


            With Me.CboImportOPFile

                DT_m_files_upload = mysql.GetMYSQLDataTable(Strsql, "m_files_upload")
                If DT_m_files_upload.Rows.Count = 0 Then
                    MsgBox("คุณต้องนำข้อมูลเข้าโปรแกรมตรวจสอบ NHSO OPPP 2554 ก่อน", MsgBoxStyle.Exclamation)
                    Exit Sub
                End If
                .DataSource = DT_m_files_upload
                .DisplayMember = "FILENAME"
                .ValueMember = "ROWID"
                Me.CboImportOPFile.SelectionStart = DT_m_files_upload.Rows(0).Item("ROWID").ToString

            End With
            If InStr(Me.CboImportOPFile.Text, "FPP", CompareMethod.Text) > 0 Then
                Me.TxtYear.Text = Mid(Me.CboImportOPFile.Text, 11, 4)
                Me.TxtMonth.Text = Mid(Me.CboImportOPFile.Text, 15, 2)
            End If
            If InStr(Me.CboImportOPFile.Text, "E_PCU", CompareMethod.Text) > 0 Then
                'E_PCU_11266_201010_20101202.zip
                Me.TxtYear.Text = Val(Mid(Me.CboImportOPFile.Text, 13, 4)) + 543
                Me.TxtMonth.Text = Mid(Me.CboImportOPFile.Text, 17, 2)
            End If
            Load_Summary()

            Strsql = "Select Amount from m_upload where filename = 'FP'"
            Dim MyAmount As Integer = mysql.MySQLExecuteScalar(Strsql)
            If MyAmount = 0 Then

                Strsql = "Select (((AMOUNT-ERROR)/amount))*100 as point from m_upload where filename = 'FP'"
                Dim MyPoint As String
                MyPoint = mysql.MySQLExecuteScalar(Strsql)
                Me.CircularGauge1.Scales(0).Value = Val(MyPoint)
                Me.CircularGauge1.Labels(0).Text = Val(MyPoint) & " %"
            Else
                Me.CircularGauge1.Scales(0).Value = 100
                Me.CircularGauge1.Labels(0).Text = " 100%"
            End If




            On_load = False

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub DockPanel3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        Try
            Dim Error_Code As String

            With Me.DataGridView1
                Dim MyPerson As New ClsPerson
                MyPerson.Get_NHSO_Profile(.Rows(e.RowIndex).Cells("PID").Value.ToString.Trim)

                Me.VGR_CID.Properties.Value = MyPerson.CID
                Me.VGR_PID.Properties.Value = .Rows(e.RowIndex).Cells("PID").Value.ToString.Trim
                Me.VGR_Fullname.Properties.Value = MyPerson.Get_Person_FullName
                Me.VGR_HN.Properties.Value = MyPerson.HN
                Me.VGR_MStatus.Properties.Value = MyPerson.MStatus
                Me.VGR_FormatCID.Properties.Value = MyPerson.Get_Person_FormatCID
                Me.VGR_Nationality.Properties.Value = MyPerson.Nation
                Me.VGR_Occupation.Properties.Value = MyPerson.Ocupaton
                Me.VGR_Race.Properties.Value = MyPerson.Race
                Me.VGR_Sex.Properties.Value = MyPerson.Sex
                Me.VGR_TypeArea.Properties.Value = MyPerson.TypeArea
                Me.VGA_BirthDay.Properties.Value = MyPerson.BirthDay
                Me.VGA_AGE.Properties.Value = MyPerson.Age

                ' Me.VGR_Fullname.Properties.Value = .Rows(e.RowIndex).Cells("CID").Value.ToString.Trim
                ' Me.VGR_CID.Properties.Value = .Rows(e.RowIndex).Cells("CID").Value.ToString.Trim
                'Me.VGR_FormatCID.Properties.Value = .Rows(e.RowIndex).Cells("HN").Value.ToString.Trim
                ' Me.VGR_PID.Properties.Value = .Rows(e.RowIndex).Cells("PID").Value.ToString.Trim
                'Me. .Properties.Value = DataGridView1.Rows(e.RowIndex).Cells("CODE_ID").Value.ToString.Trim
                'Me.Properties.Value = DataGridView1.Rows(e.RowIndex).Cells("DATE_SERV").Value.ToString.Trim
                'Me.TXTGA.Properties.Value = DataGridView1.Rows(e.RowIndex).Cells("GA").Value.ToString.Trim
                'Me.TxtANCRES.Properties.Value = DataGridView1.Rows(e.RowIndex).Cells("ANCRES").Value
                Error_Code = DataGridView1.Rows(e.RowIndex).Cells("CODE_ID").Value

                If Mid(Error_Code, 1, 1) = "," Then
                    Error_Code = Mid(Error_Code, 2, Error_Code.Length)
                End If
                If InStr(Error_Code, ",", CompareMethod.Text) > 0 Then
                    Error_Code = Replace(Error_Code, ",", "','")
                End If
                Strsql = "select code_id,remark from l_error_code where code_id in ('" & Error_Code & "')"
                Dim DT_ERROR As New DataTable
                DT_ERROR = mysql.GetMYSQLDataTable(Strsql, "l_error_code")
                With ErrorDesc_Grid
                    .DataSource = DT_ERROR
                End With
                'If Mid(Error_Code, 1, 1) = "," Then
                '    Error_Code = Mid(Error_Code, 2, Error_Code.Length - 1)
                'End If
                'If InStr(Error_Code, ",") > 0 Then
                '    Error_Code = Mid(.Rows(e.RowIndex).Cells("CODE_ID").Value, 1, InStr(.Rows(e.RowIndex).Cells("CODE_ID").Value, ",", CompareMethod.Text) - 1)
                'End If
                '  Strsql = "select remark from l_error_code where code_id in ('" & Error_Code & "')"
                ' Me.lblDescription.Text = mysql.MySQLExecuteScalar(Strsql)
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub

    Private Sub RMB_FP9205_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RMB_FP9205.ItemClick
        Load_Data("FP9205")
    End Sub

    Private Sub RMB_FP9207_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RMB_FP9207.ItemClick
        Load_Data("FP9207")
    End Sub


    Private Sub RBM_FP9240_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_FP9240.ItemClick
        Load_Data("FP9240")
    End Sub

    Private Sub RBM_FP9208_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_FP9208.ItemClick
        Load_Data("FP9208")
    End Sub

    Private Sub RBM_FP3104_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_FP3104.ItemClick
        Load_Data("FP3104")
    End Sub

    Private Sub RBM_FP3105_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_FP3105.ItemClick
        Load_Data("FP3105")
    End Sub

    Private Sub RBM_FP3130_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_FP3130.ItemClick
        Load_Data("FP3130")
    End Sub

    Private Sub RBM_FP3131_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_FP3131.ItemClick
        Load_Data("FP3131")
    End Sub

    Private Sub RBM_FP9299_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_FP9299.ItemClick
        Load_Data("FP9299")
    End Sub

    Private Sub RBM_Search_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_Search.ItemClick
        Search_Information()
    End Sub

    Private Sub DataGridView1_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick
        Search_Information()
    End Sub

    Private Sub RBM_GenScript_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_GenScript.ItemClick
        GenScript()
    End Sub

    Private Sub RBM_ExcelExport_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_ExcelExport.ItemClick
        If Me.DataGridView1.RowCount = 0 Then Exit Sub


        If Me.DataGridView1.RowCount = 0 Then Exit Sub
        If MsgBox("คุณต้องการส่งออกข้อมูลในตาราง ออกเป็นรูปแบบ Excel หรือไม่", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Export to Excel") = MsgBoxResult.No Then Exit Sub


        With SaveFileDialog1
            .FileName = Me.Text
            .Filter = "Excel 2003 | *.xls"
            If .ShowDialog() <> DialogResult.OK Then Exit Sub

            Dim Str As String = "" ' = "PCUCODE,PID,HN,CID,NAME,SEX,BIRTH,MSTATUS,OCCUPA,NATION,TYPEAREA,CODE_ID"
            For i = 0 To DataGridView1.ColumnCount - 1
                Str = Str & DataGridView1.Columns(i).HeaderText
                If i <> DataGridView1.ColumnCount - 1 Then
                    Str = Str & ","
                End If

            Next
            Using sw As StreamWriter = New StreamWriter(.FileName, False, System.Text.Encoding.UTF8)
                sw.WriteLine(Str)

                '  
                For i As Integer = 0 To DataGridView1.RowCount - 1
                    Str = ""
                    For j As Integer = 0 To DataGridView1.ColumnCount - 1
                        Str = Str & DataGridView1.Rows(i).Cells(j).Value.ToString
                        If j <> DataGridView1.ColumnCount - 1 Then
                            Str = Str & ","
                        End If

                    Next
                    sw.WriteLine(Str)
                Next
                sw.Close()

            End Using


            MsgBox("ส่งออกข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)

        End With
    End Sub

    Private Sub RBM_FP9200_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_FP9200.ItemClick
        Load_Data("FP9200")
    End Sub

    Private Sub CircularControl1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub GridView2_CustomRowCellEditForEditing(ByVal sender As System.Object, ByVal e As DevExpress.XtraGrid.Views.Grid.CustomRowCellEditEventArgs) Handles GridView2.CustomRowCellEditForEditing
        Try
            'If e.Column.FieldName = "Totalrows" Then Return
            Dim Gv As GridView = sender
            Dim CodeName As String = Gv.GetRowCellValue(e.RowHandle, _
              Gv.Columns("CODE_ID")).ToString()


            Load_Data(CodeName)
        Catch ex As Exception

        End Try
    End Sub
End Class